Summary of Python implementation of MySQL operation [Installation connection adding deleting checking etc.]

This article illustrates how Python implements MySQL operations. To share for your reference, specific as follows:

1. Install MySQLdb. Download Mysql for python from the website.

2. Import MySQLdb from Python IDLE after installation

>>>import MySQLdb

3. Create a new database connection:

>>>conn =MySQLdb.connect(host = '127.0.0.1',user= 'root',passwd='123456',db='test',port=3306,charset='utf8')
>>>

Note: the hsot parameter can also be written as host = "localhost". passwd should not be written as password. The port parameter requires int type.

4. Create a cursor:

>>>cur = conn.cursor()
>>>

5. Execute the insert single data command

>>>cur.execute("insert into msg (title,name,content) values ('python','zz','test mysql insert')")
1L # Returns the number of records affected .
>>>conn.commit() # A commit transaction insert is required to take effect .
>>>

6. Using parameters to insert multiple data commands

>>>sql = "insert into msg (title,name,content) values (%s,%s,%s)" # define 1 a sql statements 
>>>cur.executemany(sql,[('title01','name01','content01'),('title02','name02','content02')])
2L
>>>conn.commit()
>>>

7. Use for loop to splice sql command to insert multiple pieces of data

>>>sql = "insert into msg (title,name,content) values"
>>>for i in range (100):
  >>> sql += "('id" + str(i) + "'," + "'name" + str(i) + "'," + "'content" + str(i) + "'),"
>>>sql = sql[:-1] # Using slices will be the last ","  delete .
>>>cur.execute(sql)
103L
>>>conn.commit()
>>>

Execute the delete command

>>>cur.execute("delete from msg where title = 'title02' ")
1L
>>>conn.commit()
>>>

Execute the change command

>>>cur.execute("update msg set title='changedTitle' where title='title01'")
1L
>>>conn.commit()
>>>

10. Execute the query command

>>>cur.execute("select * from msg")
3L # Returns the number of records 
>>>

The query statement of python does not return the actual value accessed in the database, only the number of records obtained.

We need fetch for the cursor.

11. Use fetchone() to get a record that is the next row of data in the current cursor.

>>>cur.fetchone()
(1L, 'title01', 'name01', 'content01')

12. Use fetchmany(size=num) to obtain multiple records.

>>>conn =MySQLdb.connect(host = '127.0.0.1',user= 'root',passwd='123456',db='test',port=3306,charset='utf8')
>>>

0

Note: Here, if the query data is more than the size parameter, the same number of records as the size parameter will be obtained. If the query data record is less than the value of the size parameter, only the number of data records found will be displayed.

13. Use fetchall() to obtain all records starting from 1 row below the cursor position until the end of the query record.

>>>conn =MySQLdb.connect(host = '127.0.0.1',user= 'root',passwd='123456',db='test',port=3306,charset='utf8')
>>>

1

14. Move the cursor using scroll()

>>>conn =MySQLdb.connect(host = '127.0.0.1',user= 'root',passwd='123456',db='test',port=3306,charset='utf8')
>>>

2

Note: When mode is absolute, the cursor goes to the first parameter row in the table (or to the first row in the table if the first parameter is 1), and the number of times fetchone() takes the second row.

When mode is relative, the cursor moves down 1 row, and if the cursor is at line 3, scroll then moves to line 4.

15. Close the cursor after the database is used. Close the database connection.

>>>cur.close()
>>>conn.close

Note: The number of records returned from the execution results of the above instance and the results are related to the records in the database.

More about Python related content interested readers to view this site project: "common database operations Python skills summary", "Python data structure and algorithm tutorial", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"

I hope this article has been helpful in Python programming.